package com.test.demo;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
 


import com.macrovideo.objects.ObjectAlarmMessage;
import com.macrovideo.objects.PTZXPoint;
import com.macrovideo.sdk.custom.DeviceInfo;
import com.macrovideo.sdk.custom.RecordFileInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.util.Log;

public class DatabaseManager {
//	SQLiteDatabase database =null;
	static DatabaseHelper databaseHelper=null;
	final static String DB_NAME="macrosee_db";
	public final static String TABLE_DEVICE_INFO = "device_info";  
	public final static String TABLE_REC_FILE = "rec_info";  
	public final static String TABLE_MR_SERVER = "mr_server_info";
	public final static String TABLE_SERVER_FACE = "server_face";
	public final static String TABLE_PTZX_FACE = "ptzx_face";
	final static CursorFactory CURSOR_FACTORY=null;
	final static int DB_VERSION=8;
	private static Object lock = new Object();
	public final static String TABLE_ALARM_MESSAGE = "alarm_picture";
	public static boolean InitDataBase(Context context){
		if(databaseHelper==null){
			databaseHelper = new DatabaseHelper(context, DB_NAME, CURSOR_FACTORY, DB_VERSION);
		}
		return true;
	}
	
	public static boolean IsInfoExist(DeviceInfo info){
		boolean bResult = false;
		if(databaseHelper==null || info==null)  return bResult;
		
 		SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
		if(database==null || info==null)  return bResult;
		//ֻ�Ƚ�ip��ַ
//		String sql ="select count(*) from "+TABLE_DEVICE_INFO+" where ip='"+info.strIP+"' and port="+info.nPort+" and username ='"+info.strUsername+"'";       
//		String sql ="select count(*) from "+TABLE_DEVICE_INFO+" where dev_id="+info.nDevID+" and save_type="+Defines.SERVER_SAVE_TYPE_SEARCH;  //note by luo 20141227
		String sql ="select count(*) from "+TABLE_DEVICE_INFO+" where dev_id="+info.getnDevID();//modify by luo 20141227
 		Cursor cursor = null;
		try {      
			cursor = database.rawQuery(sql, null);
 		} catch (SQLException e) {       
 			
// 			//@@System.out.println("IsInfoExist: SQLException");//add for test
			return bResult;
		}       
				
		int count=cursor.getCount();
		if(count>0){
 
			 
			try{
				cursor.moveToFirst();
				int nCount = cursor.getInt(0);
//				//@@System.out.println("IsInfoExist: nCount="+nCount);//add for test
				if(nCount>0){
					bResult=true;
				}
			}catch(SQLException e){
				//@@System.out.println("IsInfoExist err:"+e);
			}catch(Exception e){
				//@@System.out.println("IsInfoExist err:"+e);
			}
	
		}
		
		try {      
			if(cursor!=null){
				cursor.close();
				cursor=null;
			}
			if(database!=null){
				database.close();
				database=null;
			}
 		} catch (SQLException e) {       

		}       
		
		return bResult;
	}
 
	public static boolean AddServerInfo(DeviceInfo info){
		Log.w("AddServerInfo", "AddServerInfo: "+info.getnDevID());
		boolean bResult = false;
		if(databaseHelper==null || info==null)  return bResult;
		Log.w("AddServerInfo", "to AddServerInfo: "+info.getnDevID());
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null || info==null) {
				Log.w("AddServerInfo", "database==null");
				return bResult; 
			}
 
			String sql ="insert into "+TABLE_DEVICE_INFO+" (dev_id, name, ip, port, username, password, domain, save_type, online_stat, online_stat_time,face) values ('"+info.getnDevID()+
					"','"+info.getStrName()+
					"','"+info.getStrIP()+
					"','"+info.getnPort()+
					"','"+info.getStrUsername()+
					"','"+info.getStrPassword()+
					"','"+info.getStrDomain()+
					"','"+info.getnSaveType()+
					"','"+info.getnOnLineStat()+
					"','"+info.getlOnLineStatChaneTime()+
					"','"+info.getFaceImage() +
					"')";   
			
	 		try {      
				database.execSQL(sql); 
				bResult = true;
	 		} catch (SQLException e) {       
	 			Log.w("SQLException", e.toString());
				bResult = false;
			}       
	 		
	 		try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
	 			 
			}       
	 		
		}
 		
		return bResult;
	}
	
	public static boolean modifyServerInfo(DeviceInfo info){
		boolean bResult = false;
		if(databaseHelper==null || info==null)  return bResult;
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null || info==null)  return bResult; 

//			
			String sql = "update "+TABLE_DEVICE_INFO+" set dev_id="+info.getnDevID()+",name='"+info.getStrName()+"',ip='"+info.getStrIP()+"',port="+info.getnPort()+",username='"+info.getStrUsername()+"',password='"+info.getStrPassword()+"',domain='"+info.getStrDomain()+"',save_type="+info.getnSaveType()+" where id="+info.getnID();

	 		try {      
				database.execSQL(sql); 
				bResult = true;
	 		} catch (SQLException e) {        
				bResult = false;
			}       
	 		try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		      
			}       
		}
		return bResult;
	}
	
	public static boolean UpdateServerInfo(DeviceInfo info){
		boolean bResult = false;
		if(databaseHelper==null || info==null)  return bResult;
		
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null || info==null)  return bResult; 

			String sql = "update "+TABLE_DEVICE_INFO+" set ip='"+info.getStrIP()+"',port="+info.getnPort()+" ,online_stat="+info.getnOnLineStat()+" ,online_stat_time="+info.getlOnLineStatChaneTime()+" where dev_id="+info.getnDevID()+" and save_type="+LocalDefines.SERVER_SAVE_TYPE_SEARCH;
		 
			System.out.println("UpdateServerInfo "+sql);//add for test
	 		try {      
				database.execSQL(sql); 
				bResult = true;
				
	 		} catch (SQLException e) {        
				bResult = false;
				System.out.println("UpdateServerInfo err"+e.toString());//add for test 
			}       
	 		try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
			
		}
		return bResult;
	}
	public static boolean UpdateServerInfoState(DeviceInfo info){
		boolean bResult = false;
		if(databaseHelper==null || info==null)  return bResult;
	 
		 
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
	        try{
//	         
 	    		
	    		ContentValues values = new ContentValues();
	        	values.put("ip", info.getStrIP());
	        	values.put("online_stat", info.getnOnLineStat());
	    		values.put("online_stat_time", info.getlOnLineStatChaneTime());
	    		values.put("save_type", info.getnSaveType());
	    		String[] whereArgs = {String.valueOf(info.getnDevID())};
	        	String whereClause = "dev_id=?";


	        	database.update(TABLE_DEVICE_INFO, values, whereClause, whereArgs);
	        	
	        	
	        } catch (SQLException ex) {  
	        	//@@System.out.println("UpdateServerInfoIP for id_"+info.getnDevID()+" fail: "+ex.toString());//add for test
	        }  
			
	  		 
	        try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
		}
		return bResult;
	}
	
 
	
	public static boolean UpdateServerInfoMsgCount(int nDevID, long lLastFreshTime, long lLastGetTime, int nMsgCount, String strUsername, String strPassword){
		boolean bResult = false;
		if(databaseHelper==null || nDevID<0) return false;
		 
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
	        try{
//	         
	    		//@@System.out.println("UpdateServerInfoMsgCount for id_"+nDevID+","+ lLastFreshTime+","+lLastGetTime+","+nMsgCount);//add for test
	    		
	    		ContentValues values = new ContentValues();
	        	values.put("last_fresh_time", lLastFreshTime);
	        	values.put("last_get_time", lLastGetTime);
	        	values.put("new_msg_count", nMsgCount);
	       
	        	String[] whereArgs = {String.valueOf(nDevID), strUsername, strPassword};
	        	String whereClause = "dev_id=? and username=? and password=?";

	        	database.update(TABLE_DEVICE_INFO, values, whereClause, whereArgs);
	        } catch (SQLException ex) {  
	        	//@@System.out.println("UpdateServerInfoMsgCount for id_"+nDevID+" fail: "+ex.toString());//add for test
	        }  
			
	  		 
	        try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
		}
 		
		
 		return bResult;
	}
	
	public static boolean UpdateServerInfoMsgFreshTime(int nDevID, long lLastFreshTime){
		boolean bResult = false;
		if(databaseHelper==null || nDevID<0) return false;
		 
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
	        try{
//	         
	    		//@@System.out.println("UpdateServerInfoMsgCount for id_"+nDevID+","+ lLastFreshTime+","+lLastGetTime+","+nMsgCount);//add for test
	    		
	    		ContentValues values = new ContentValues();
	        	values.put("last_fresh_time", lLastFreshTime);
 	        	values.put("new_msg_count", 1);
	       
	        	String[] whereArgs = {String.valueOf(nDevID)};
	        	String whereClause = "dev_id=?";

	        	database.update(TABLE_DEVICE_INFO, values, whereClause, whereArgs);
	        } catch (SQLException ex) {  
	        	//@@System.out.println("UpdateServerInfoMsgCount for id_"+nDevID+" fail: "+ex.toString());//add for test
	        }  
			
	  		 
	        try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
		}
 		
		
 		return bResult;
	}
	
	public static boolean DeleteServerInfo(DeviceInfo info){
		boolean bResult = false;
		if(databaseHelper==null || info==null)  return bResult; 
		
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null || info==null)  return bResult; 
	 
			String sql ="delete from "+TABLE_DEVICE_INFO+" where id="+info.getnID();   
	 		try {      
				database.execSQL(sql); 
				bResult = true;
	 		} catch (SQLException e) {        
				bResult = false;
			}       
	 		try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
		}
		
				
		return bResult;
	}
 
	public static boolean ClearServerInfo(){
		boolean bResult = false;
		if(databaseHelper==null)  return bResult; 
		
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
			String sql ="delete from "+TABLE_DEVICE_INFO;   
	 		try {      
				database.execSQL(sql); 
				bResult = true;
	 		} catch (SQLException e) {        
				bResult = false;
			}       
	 		try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
		}
		
		
		////@@System.out.println("ClearServerInfo result="+bResult);//add for test
		
		return bResult;
	}
	
	public static boolean setFaceForID(int nID, Bitmap face){
		
 		
		boolean bResult = false;
		if(databaseHelper==null || nID<0 || face==null) return false;
		 
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
	        try{
//	        
	        	ByteArrayOutputStream os = new ByteArrayOutputStream();
	        	  
	    		face.compress(Bitmap.CompressFormat.JPEG, 70, os); 
	    		
	    		//@@System.out.println("setFace for id_"+nID+" face size:"+os.toByteArray().length);//add for test
	    		
	        	ContentValues values = new ContentValues();
	        	values.put("face", os.toByteArray());
	        	String[] whereArgs = {String.valueOf(nID)};
	        	String whereClause = "id=?";
	        	database.update(TABLE_DEVICE_INFO, values, whereClause, whereArgs);
	        } catch (SQLException ex) {  
	        	//@@System.out.println("setFace for id_"+nID+" fail: "+ex.toString());//add for test
	        }  
			
	       
	        try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
		}
 		
		
 		return bResult;
 	}
	
	public static boolean setFaceForDevID(int nDevID, Bitmap face, String strUsername, String strPassword){
		
 		
		boolean bResult = false;
		if(databaseHelper==null || nDevID<0 || face==null) return false;
		 
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
	        try{

	        	ByteArrayOutputStream os = new ByteArrayOutputStream();
	    		face.compress(Bitmap.CompressFormat.JPEG, 70, os); 
	    		
//	    		//@@System.out.println("setFaceForDevID for dev_id_"+nDevID+" face size:"+os.toByteArray().length);//add for test
	    		
	        	ContentValues values = new ContentValues();
	        	values.put("face", os.toByteArray());
	        	String[] whereArgs = {String.valueOf(nDevID), String.valueOf(strUsername), String.valueOf(strPassword)};
	        	String whereClause = " dev_id=? and username=? and password=?";
	        	database.update(TABLE_DEVICE_INFO, values, whereClause, whereArgs);
	        	 
	        } catch (SQLException ex) {  
	        	//@@System.out.println("setFace for id_"+nDevID+" fail:"+ex.toString());//add for test
	        }  
			
	        try{
	        	database.close();  
	        	database=null;
	        } catch (SQLException ex) {  
	        	//@@System.out.println("setFace for id_"+nDevID+" fail:"+ex.toString());//add for test
	        }  
		}
 		
 		return bResult;
 	}
	
	public static DeviceInfo[] GetAllServerInfoWithoutImage(){
		DeviceInfo [] serverList=null;
		boolean isErr=false;
		if(databaseHelper==null)  return null; 
		
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getReadableDatabase();    
			if(database==null)  return null; 
			
	 		
	 		
			String sql ="select * from "+TABLE_DEVICE_INFO+" order by id desc";        
	 		Cursor cursor = null;
			try {      
				cursor = database.rawQuery(sql, null);
	 		} catch (SQLException e) {       
	 			
	 			//@@System.out.println("GetAllServerInfo SQLException: "+e.toString());
				return null;
			}       
					
			int count=cursor.getCount();
			if(count>0){
				serverList=new DeviceInfo[count];
			 
				int idIndex = cursor.getColumnIndex("id"); 
				int devIdIndex = cursor.getColumnIndex("dev_id"); 
				int nameIndex = cursor.getColumnIndex("name");
				int serverIndex = cursor.getColumnIndex("ip"); 
				int portIndex = cursor.getColumnIndex("port"); 
				int usernameIndex = cursor.getColumnIndex("username"); 
				int passwordIndex = cursor.getColumnIndex("password"); 
				int domainIndex = cursor.getColumnIndex("domain"); 
				int saveTypeIndex = cursor.getColumnIndex("save_type"); 
				
				int newMsgCountIndex = cursor.getColumnIndex("new_msg_count"); //add by luo 20141015
				int lastFreshIndex = cursor.getColumnIndex("last_fresh_time"); //add by luo 20141015
				int lastGetIndex = cursor.getColumnIndex("last_get_time"); //add by luo 20141015
				int faceIndex = cursor.getColumnIndex("face");//add by luo 20141008
	 
				 
				int i=0;
				try{
					for(cursor.moveToFirst();!(cursor.isAfterLast()) && i<count;cursor.moveToNext()){  
						
	 							
						int nID = cursor.getInt(idIndex);
	 					int nDevID = cursor.getInt(devIdIndex);
	  					String strName = cursor.getString(nameIndex);
	 					String strServer = cursor.getString(serverIndex);     
						int nPort = cursor.getInt(portIndex);
	 					String strUsername = cursor.getString(usernameIndex);
						String strPassword = cursor.getString(passwordIndex);
						String strDomain = cursor.getString(domainIndex);
						int nSaveType = cursor.getInt(saveTypeIndex);
						
						int nNewMsgCount = cursor.getInt(newMsgCountIndex);//add by luo 20141015
						long lLastFreshTime = cursor.getLong(lastFreshIndex);//add by luo 20141015
						long lGetFreshTime = cursor.getLong(lastGetIndex);//add by luo 20141015
						
					 
						
//						//@@System.out.println("GetAllServerInfo :"+strName+"("+nDevID+") _ "+lLastFreshTime+", "+lGetFreshTime+", "+nNewMsgCount);
						
						//modify by luo 20141008
						serverList[i] = new DeviceInfo(nID, nDevID, strName, strServer, nPort, strUsername, strPassword, strDomain, nSaveType, lLastFreshTime, lGetFreshTime, nNewMsgCount, 0, 0,null);
 						i++;         
						
	 					
					}
				}catch(SQLException e){
					//@@System.out.println("GetAllServerInfo 6err:"+e);
					isErr=true;
				}catch(Exception e){
					//@@System.out.println("GetAllServerInfo 6err:"+e);
					isErr=true;
				}
			}
			
			try {      
				if(cursor!=null){
					cursor.close();
					cursor=null;
				}
				if(database!=null){
					database.close();
					database=null;
				}
	 		} catch (SQLException e) {       
	 			isErr=true;
			}       
			
		}
		
		if(isErr==true) return null;
		
		return serverList;
	}
	 
	
	//====add by mai 2015-1-28==============
			/**
			 * ����id���в�ѯ�豸��Ϣ
			 * @param DevID
			 * @return
			 */
		public static DeviceInfo GetServerInfo(int DevID){
			DeviceInfo serverList=null;
			boolean isErr=false;
			if(databaseHelper==null)  return null; 
				
				synchronized (lock) {
					SQLiteDatabase database = databaseHelper.getReadableDatabase();    
					if(database==null)  return null; 
					
			 		
			 		
					String sql ="select * from "+TABLE_DEVICE_INFO+" where dev_id="+DevID;        
			 		Cursor cursor = null;
					try {      
						cursor = database.rawQuery(sql, null);
			 		} catch (SQLException e) {       
			 			
			 			//@@System.out.println("GetAllServerInfo SQLException: "+e.toString());
						return null;
					}       
							
					int count=cursor.getCount();
					if(count>0){
						serverList = new DeviceInfo();
					 
						int idIndex = cursor.getColumnIndex("id"); 
						int devIdIndex = cursor.getColumnIndex("dev_id"); 
						int nameIndex = cursor.getColumnIndex("name");
						int serverIndex = cursor.getColumnIndex("ip"); 
						int portIndex = cursor.getColumnIndex("port"); 
						int usernameIndex = cursor.getColumnIndex("username"); 
						int passwordIndex = cursor.getColumnIndex("password"); 
						int domainIndex = cursor.getColumnIndex("domain"); 
						int saveTypeIndex = cursor.getColumnIndex("save_type"); 
						
						int newMsgCountIndex = cursor.getColumnIndex("new_msg_count"); //add by luo 20141015
						int lastFreshIndex = cursor.getColumnIndex("last_fresh_time"); //add by luo 20141015
						int lastGetIndex = cursor.getColumnIndex("last_get_time"); //add by luo 20141015
						int faceIndex = cursor.getColumnIndex("face");//add by luo 20141008
			 
						 
						int i=0;
						
						try{
							for(cursor.moveToFirst();!(cursor.isAfterLast()) && i<count;cursor.moveToNext()){  
								
			 							
								int nID = cursor.getInt(idIndex);
			 					int nDevID = cursor.getInt(devIdIndex);
			  					String strName = cursor.getString(nameIndex);
			 					String strServer = cursor.getString(serverIndex);     
								int nPort = cursor.getInt(portIndex);
			 					String strUsername = cursor.getString(usernameIndex);
								String strPassword = cursor.getString(passwordIndex);
								String strDomain = cursor.getString(domainIndex);
								int nSaveType = cursor.getInt(saveTypeIndex);
								
								int nNewMsgCount = cursor.getInt(newMsgCountIndex);//add by luo 20141015
								long lLastFreshTime = cursor.getLong(lastFreshIndex);//add by luo 20141015
								long lGetFreshTime = cursor.getLong(lastGetIndex);//add by luo 20141015
								
								//add by luo 20141008
								byte [] faceData= cursor.getBlob(faceIndex);
								Bitmap faceImage = null;
								if(faceData!=null && faceData.length>256){
															
									try{
										faceImage =BitmapFactory.decodeByteArray(faceData, 0, faceData.length);
									}catch(OutOfMemoryError e){
										faceImage = null;
									}
									
								}
								serverList = new DeviceInfo(nID, nDevID, strName, "192.168.1.1", nPort, strUsername, strPassword, strDomain, nSaveType, lLastFreshTime, lGetFreshTime, nNewMsgCount,  0, 0,faceImage);
								faceData=null;
								i++;         
								
			 					
							}
						}catch(SQLException e){
							//@@System.out.println("GetAllServerInfo 6err:"+e);
							isErr=true;
						}catch(Exception e){
							//@@System.out.println("GetAllServerInfo 6err:"+e);
							isErr=true;
						}
						
						
						 
						
					}
					
					try {      
						if(cursor!=null){
							cursor.close();
							cursor=null;
						}
						if(database!=null){
							database.close();
							database=null;
						}
			 		} catch (SQLException e) {       
			 			isErr=true;
					}       
					
				}
				
				if(isErr==true) return null;
				
				return serverList;
			}
				
			//====end add by mai 2015-1-28=========
	
	
	
	public static DeviceInfo[] GetAllServerInfo(){
		DeviceInfo [] serverList=null;
		boolean isErr=false;
		if(databaseHelper==null)  return null; 
		
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getReadableDatabase();    
			if(database==null)  return null; 
			
	 		
	 		
			String sql ="select * from "+TABLE_DEVICE_INFO+" order by id desc";        
	 		Cursor cursor = null;
			try {      
				cursor = database.rawQuery(sql, null);
	 		} catch (SQLException e) {       
	 			
	 			//@@System.out.println("GetAllServerInfo SQLException: "+e.toString());
				return null;
			}       
					
			int count=cursor.getCount();
			if(count>0){
				serverList=new DeviceInfo[count];
			 
				int idIndex = cursor.getColumnIndex("id"); 
				int devIdIndex = cursor.getColumnIndex("dev_id"); 
				int nameIndex = cursor.getColumnIndex("name");
				int serverIndex = cursor.getColumnIndex("ip"); 
				int portIndex = cursor.getColumnIndex("port"); 
				int usernameIndex = cursor.getColumnIndex("username"); 
				int passwordIndex = cursor.getColumnIndex("password"); 
				int domainIndex = cursor.getColumnIndex("domain"); 
				int saveTypeIndex = cursor.getColumnIndex("save_type"); 
				
				int newMsgCountIndex = cursor.getColumnIndex("new_msg_count"); //add by luo 20141015
				int lastFreshIndex = cursor.getColumnIndex("last_fresh_time"); //add by luo 20141015
				int lastGetIndex = cursor.getColumnIndex("last_get_time"); //add by luo 20141015
				int onlineStat = cursor.getColumnIndex("online_stat"); //add by luo 20150711
				int onlineTimeIndex = cursor.getColumnIndex("online_stat_time"); //add by 20150711
				int faceIndex = cursor.getColumnIndex("face");//add by luo 20141008
			 
				long lTimeNow = System.currentTimeMillis();
				int i=0;
				try{
					for(cursor.moveToFirst();!(cursor.isAfterLast()) && i<count;cursor.moveToNext()){  
						
	 							
						int nID = cursor.getInt(idIndex);
	 					int nDevID = cursor.getInt(devIdIndex);
	  					String strName = cursor.getString(nameIndex);
	 					String strServer = cursor.getString(serverIndex);     
						int nPort = cursor.getInt(portIndex);
	 					String strUsername = cursor.getString(usernameIndex);
						String strPassword = cursor.getString(passwordIndex);
						String strDomain = cursor.getString(domainIndex);
						int nSaveType = cursor.getInt(saveTypeIndex);
						
						int nNewMsgCount = cursor.getInt(newMsgCountIndex);//add by luo 20141015
						long lLastFreshTime = cursor.getLong(lastFreshIndex);//add by luo 20141015
						long lGetFreshTime = cursor.getLong(lastGetIndex);//add by luo 20141015
						
						int nOnlineStat = cursor.getInt(onlineStat);//add by luo 20150711
						long lOnlineTime = cursor.getLong(onlineTimeIndex);//add by luo 20150711
				 
                      
						
						if(lTimeNow - lOnlineTime>=50000){//add by luo 20150711
							lOnlineTime = 0;
							nOnlineStat = LocalDefines.ON_LINE_STAT_READY;
						}
						//add by luo 20141008
						byte [] faceData= cursor.getBlob(faceIndex);
						Bitmap faceImage = null;
						if(faceData!=null && faceData.length>256){
													
							try{
								faceImage =BitmapFactory.decodeByteArray(faceData, 0, faceData.length);
							}catch(OutOfMemoryError e){
								faceImage = null;
							}
							
						}
						//end add by luo 20141008
						
						 System.out.println("GetAllServerInfo :"+strName+"("+nDevID+") _ "+lLastFreshTime+", "+lGetFreshTime+", "+nNewMsgCount+", "+nSaveType);
						
						//modify by luo 20141008
						serverList[i] = new DeviceInfo(nID, nDevID, strName, strServer, nPort, strUsername, strPassword, strDomain, nSaveType, lLastFreshTime, lGetFreshTime, nNewMsgCount, nOnlineStat,lOnlineTime, faceImage);
						faceData=null;
 
						i++;         
						
						
					}
				}catch(SQLException e){
					//@@System.out.println("GetAllServerInfo 6err:"+e);
					isErr=true;
				}catch(Exception e){
					//@@System.out.println("GetAllServerInfo 6err:"+e);
					isErr=true;
				}
				
				
				 
				
			}
			
			try {      
				if(cursor!=null){
					cursor.close();
					cursor=null;
				}
				if(database!=null){
					database.close();
					database=null;
				}
	 		} catch (SQLException e) {       
	 			isErr=true;
			}       
			
		}
		
		if(isErr==true) return null;
		
		return serverList;
	}
	
	public static DeviceInfo[] GetServerInfos(int count){
		DeviceInfo [] serverList=null;
		
		if(databaseHelper==null)  return null; 
		SQLiteDatabase database = databaseHelper.getReadableDatabase();    
		if(database==null)  return null; 
		
		String sql ="select  *  from "+TABLE_DEVICE_INFO+" order by id desc limit "+count; 
		Cursor cursor = null;
		try {      
			cursor = database.rawQuery(sql, null);
 		} catch (SQLException e) {       
 			
 			//@@System.out.println("GetAllServerInfo SQLException: "+e.toString());
			return null;
		}       
		
 		
		int maxCount=cursor.getCount();
		if(maxCount<count) count=maxCount;//
		
		if(count>0){
			serverList=new DeviceInfo[count];
		 
			int idIndex = cursor.getColumnIndex("id"); 
			int devIdIndex = cursor.getColumnIndex("dev_id"); 
			int nameIndex = cursor.getColumnIndex("name");
			int serverIndex = cursor.getColumnIndex("ip"); 
			int portIndex = cursor.getColumnIndex("port"); 
			int usernameIndex = cursor.getColumnIndex("username"); 
			int passwordIndex = cursor.getColumnIndex("password"); 
			int domainIndex = cursor.getColumnIndex("domain"); 
			int saveTypeIndex = cursor.getColumnIndex("save_type"); 
			int faceIndex = cursor.getColumnIndex("face");//add by luo 20141008
 
 			int i=0;
			try{
				for(cursor.moveToFirst();!(cursor.isAfterLast()) && i<count;cursor.moveToNext()){  
					
 							
					
					int nID = cursor.getInt(idIndex);
 					int nDevID = cursor.getInt(devIdIndex);
  					String strName = cursor.getString(nameIndex);
 					String strServer = cursor.getString(serverIndex);     
					int nPort = cursor.getInt(portIndex);
 					String strUsername = cursor.getString(usernameIndex);
					String strPassword = cursor.getString(passwordIndex);
					String strDomain = cursor.getString(domainIndex);
					int nSaveType = cursor.getInt(saveTypeIndex);
 
					//add by luo 20141008
					byte [] faceData= cursor.getBlob(faceIndex);
					Bitmap faceImage = null;
					if(faceData!=null && faceData.length>256){
						
						try{
							faceImage =BitmapFactory.decodeByteArray(faceData, 0, faceData.length);
						}catch(OutOfMemoryError e){
							faceImage = null;
						}
						
					}
					//end add by luo 20141008
					
					//modify by luo 20141008
					serverList[i] = new DeviceInfo(nID, nDevID, strName, strServer, nPort, strUsername, strPassword, strDomain, nSaveType, faceImage);
			        
					i++;         
				}
			}catch(SQLException e){
				//@@System.out.println("GetAllServerInfo 6err:"+e);
			}catch(Exception e){
				//@@System.out.println("GetAllServerInfo 6err:"+e);
			}
			
			
			 
			
		}
		
		try {      
			if(cursor!=null){
				cursor.close();
				cursor=null;
			}
			if(database!=null){
				database.close();
				database=null;
			}
 		} catch (SQLException e) {       

		}       
		
		
		return serverList;
	}
	
	public static DeviceInfo GetServerInfoByID(int nId){
		DeviceInfo serverinfo=null;
		
		if(databaseHelper==null)  return null; 
		SQLiteDatabase database = databaseHelper.getReadableDatabase();    
		if(database==null)  return null; 
		
		String sql ="select  *  from "+TABLE_DEVICE_INFO+" where id="+nId; 
		Cursor cursor = null;
		try {      
			cursor = database.rawQuery(sql, null);
 		} catch (SQLException e) {       
 			
 			//@@System.out.println("GetAllServerInfo SQLException: "+e.toString());
			return null;
		}       
 
		
		if(cursor.getCount()>0){
			
		 
			int idIndex = cursor.getColumnIndex("id"); 
			int devIdIndex = cursor.getColumnIndex("dev_id"); 
			int nameIndex = cursor.getColumnIndex("name");
			int serverIndex = cursor.getColumnIndex("ip"); 
			int portIndex = cursor.getColumnIndex("port"); 
			int usernameIndex = cursor.getColumnIndex("username"); 
			int passwordIndex = cursor.getColumnIndex("password"); 
			int domainIndex = cursor.getColumnIndex("domain"); 
			int saveTypeIndex = cursor.getColumnIndex("save_type"); 
			int faceIndex = cursor.getColumnIndex("face");//add by luo 20141008
 
 			try{
				for(cursor.moveToFirst();!(cursor.isAfterLast());){
					int nID = cursor.getInt(idIndex);
 					int nDevID = cursor.getInt(devIdIndex);
  					String strName = cursor.getString(nameIndex);
 					String strServer = cursor.getString(serverIndex);     
					int nPort = cursor.getInt(portIndex);
 					String strUsername = cursor.getString(usernameIndex);
					String strPassword = cursor.getString(passwordIndex);
					String strDomain = cursor.getString(domainIndex);
					int nSaveType = cursor.getInt(saveTypeIndex);
					//add by luo 20141008
					byte [] faceData= cursor.getBlob(faceIndex);
					Bitmap faceImage = null;
					if(faceData!=null && faceData.length>256){
						
						try{
							faceImage =BitmapFactory.decodeByteArray(faceData, 0, faceData.length);
						}catch(OutOfMemoryError e){
							faceImage = null;
						}
					}
					//end add by luo 20141008
					//modify by luo 20141008
					serverinfo = new DeviceInfo(nID, nDevID, strName, strServer, nPort, strUsername, strPassword, strDomain, nSaveType, faceImage);
					break;      
				}
			}catch(SQLException e){
				//@@System.out.println("GetAllServerInfo 6err:"+e);
			}catch(Exception e){
				//@@System.out.println("GetAllServerInfo 6err:"+e);
			}
						
		}
		
		try {      
			if(cursor!=null){
				cursor.close();
				cursor=null;
			}
			if(database!=null){
				database.close();
				database=null;
			}
 		} catch (SQLException e) {       

		}       
		
		
		return serverinfo;
	}
	
	//
	public static boolean ClearRecInfos(){
		boolean bResult = false;
		if(databaseHelper==null)  return bResult; 
		
		SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
		if(database==null)  return bResult; 
 
		String sql ="delete from "+TABLE_REC_FILE;   
 		try {      
			database.execSQL(sql); 
			bResult = true;
 		} catch (SQLException e) {  
 			//@@System.out.println("ClearRecInfos ERR= "+e.toString());//
 	       	
			bResult = false;
		}       
 		database.close();  
		database=null;
		
		////@@System.out.println("ClearServerInfo result="+bResult);//add for test
		
		return bResult;
	}
	public static int SaveRecInfos(ArrayList<RecordFileInfo> recFileList){
		int nCount=0;
		if(databaseHelper==null || recFileList==null || recFileList.size()<=0)  return nCount;
		synchronized (lock) {
	 		SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null || recFileList==null)  return nCount;
			 database.beginTransaction();
			 try {
				 RecordFileInfo info=null;
					for(int i=recFileList.size()-1;i>=0; i--){
						 info= recFileList.get(i);
						 if(info!=null){
							 String sql ="insert into "+TABLE_REC_FILE+" (file_id, file_size, name, start_hour, start_min, start_sec, file_time_len) values ('"+info.getnFileID()+
										"','"+info.getnFileSize()+
										"','"+info.getStrFileName()+
										"','"+info.getuStartHour()+
										"','"+info.getuStartMin()+
										"','"+info.getuStartSec()+
										"','"+info.getuFileTimeLen()+
										"')";   
						 		try {      
									database.execSQL(sql); 
									nCount++;
						 		} catch (SQLException e) {        
									 break;
								}       
						 }
						 info=null;
					}

				 database.setTransactionSuccessful();//���ô˷�������ִ�е�endTransaction() ʱ�ύ��ǰ������������ô˷�����ع�����
			   } finally {
				   database.endTransaction();//������ı�־�������ύ���񣬻��ǻع�����
			   }
			 try {      
		 
					if(database!=null){
						database.close();
						database=null;
					}
		 		} catch (SQLException e) {       

				}       
		}
		return nCount;
	}
	public static RecordFileInfo[] GetAllRecInfo(){
		RecordFileInfo [] recFileList=null;
				
		//@@System.out.println("GetAllRecInfo 1");
		
		if(databaseHelper==null)  return null; 
		//@@System.out.println("GetAllRecInfo 2");
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getReadableDatabase();    
			if(database==null)  return null; 
			
	 		
			String sql ="select * from "+TABLE_REC_FILE+" order by id desc";        
	 		Cursor cursor = null;
			try {      
				cursor = database.rawQuery(sql, null);
	 		} catch (SQLException e) {       
	 			
	 			//@@System.out.println("GetAllRecInfo SQLException: "+e.toString());
				return null;
			}       
					
			int count=cursor.getCount();
			
			//@@System.out.println("GetAllRecInfo count: "+cursor.getCount());
			
			if(count>0){
				
				
				recFileList=new RecordFileInfo[count];
			 
				int idIndex = cursor.getColumnIndex("id"); 
				int fileIdIndex = cursor.getColumnIndex("file_id"); 
				int fileSizeIndex = cursor.getColumnIndex("file_size"); 
				int nameIndex = cursor.getColumnIndex("name");
				int startHourIndex = cursor.getColumnIndex("start_hour");
				int startMinIndex = cursor.getColumnIndex("start_min"); 
				int startSecIndex = cursor.getColumnIndex("start_sec"); 
				int timeLenIndex = cursor.getColumnIndex("file_time_len"); 
		  
	 
				 
				int i=0;
				try{
					for(cursor.moveToFirst();!(cursor.isAfterLast()) && i<count;cursor.moveToNext()){  
		 
						int nID = cursor.getInt(idIndex);
	 					int nFileID = cursor.getInt(fileIdIndex);
	 					int nFileSize = cursor.getInt(fileSizeIndex);
	  					String strName = cursor.getString(nameIndex);
	 					 
						int nStartHour = cursor.getInt(startHourIndex);
						int nStartMin = cursor.getInt(startMinIndex);
						int nStartSec = cursor.getInt(startSecIndex);
						int nTimeLen = cursor.getInt(timeLenIndex);
	 					 

						recFileList[i] = new RecordFileInfo(nFileID, nFileSize, nStartHour, nStartMin, nStartSec, nTimeLen, strName);
	  	   
						i++;         
						
//						//@@System.out.println("Get : "+nID+", "+nDevID+", "+strName+", "+strServer+", "+nPort+", "+strUsername+", "+strPassword+", "+strDomain);//add for test
						
					}
				}catch(SQLException e){
					//@@System.out.println("GetAllRecInfo 6err:"+e);
				}catch(Exception e){
					//@@System.out.println("GetAllRecInfo 6err:"+e);
				}
				
				
				 
				
			}
			
			try {      
				if(cursor!=null){
					cursor.close();
					cursor=null;
				}
				if(database!=null){
					database.close();
					database=null;
				}
	 		} catch (SQLException e) {       

			}       
		}
		
		
		
		return recFileList;
	}
	
	 
  public static boolean DeleteServerFace(int nServerID){
		boolean bResult = false;
		if(databaseHelper==null)  return bResult; 
		

		SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
		if(database==null)  return bResult; 
 
		String sql ="delete from "+TABLE_SERVER_FACE+" where server_id="+nServerID;   
 		try {      
			database.execSQL(sql); 
			bResult = true;
 		} catch (SQLException e) {        
			bResult = false;
		}       
 		database.close();  
		database=null;
				
		return bResult;
	}
	 
	public static boolean ClearServerFace(int nServerID){
		boolean bResult = false;
		if(databaseHelper==null)  return bResult; 
		

		SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
		if(database==null)  return bResult; 
 
		String sql ="delete from "+TABLE_SERVER_FACE;   
 		try {      
			database.execSQL(sql); 
			bResult = true;
 		} catch (SQLException e) {        
			bResult = false;
		}       
 		database.close();  
		database=null;
				
		return bResult;
	}
	
	//add by luo mai
	
  
	//add by luo 20141020
	//add alar message
	public static boolean AddAlarmMessage(ObjectAlarmMessage info){
		 
		boolean bResult = false;
		if(databaseHelper==null || info==null)  return bResult;
 
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
	        try{
//	    	    		
	        	ContentValues values = new ContentValues();
	        	Bitmap image= info.getImage();
	        	if(image!=null){
	        		ByteArrayOutputStream os = new ByteArrayOutputStream();
		        	  
	        		image.compress(Bitmap.CompressFormat.JPEG, 70, os); 
	        		values.put("image", os.toByteArray());
	        	} 
	        	values.put("save_id", info.getnSaveID());
	        	values.put("alarm_id", info.getnAlarmID());
	        	values.put("dev_id", info.getnDevID());
	        	values.put("alarm_type", info.getnAlarmType());
	        	values.put("alarm_level", info.getnAlarmLevel());
	        	values.put("alarm_msg", info.getStrAlarmMsg());
	        	values.put("alarm_time", info.getStrAlarmTime());
	        	values.put("save_time", info.getLSaveTime());
	       	 
 	        	
	        	database.insert(TABLE_ALARM_MESSAGE, null, values);
	        	
 	 
	        	
	        } catch (SQLException ex) {  
	        	//@@System.out.println("AddAlarmMessage for alarm msg"+ info.getnDevID()+" fail: "+ex.toString());//add for test
	        }  
			
	       
	        try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
		}
		return bResult;
	}
 
	public static ObjectAlarmMessage[] GetAlarmMessage(){
		ObjectAlarmMessage [] messageList=null;
				
		if(databaseHelper==null)  return null; 
		SQLiteDatabase database = databaseHelper.getReadableDatabase();    
		if(database==null)  return null; 
		
		synchronized (lock) {
			String sql ="select * from "+TABLE_ALARM_MESSAGE+" order by id desc";        
	 		Cursor cursor = null;
			try {      
				cursor = database.rawQuery(sql, null);
	 		} catch (SQLException e) {       
	 			
	 			//@@System.out.println("GetAllServerInfo SQLException: "+e.toString());
				return null;
			}       
					
	 
			int count=cursor.getCount();
			if(count>0){
				messageList=new ObjectAlarmMessage[count];
			 
				int idIndex = cursor.getColumnIndex("id"); 
				int saveIndex = cursor.getColumnIndex("save_id"); 
				int alarmIndex = cursor.getColumnIndex("alarm_id"); 
				int devIndex = cursor.getColumnIndex("dev_id"); 
				int alarmTypeIndex = cursor.getColumnIndex("alarm_type");
				int alarmLevelIndex = cursor.getColumnIndex("alarm_level");
				int alarmMsgIndex = cursor.getColumnIndex("alarm_msg"); 
				int alarmTimeIndex = cursor.getColumnIndex("alarm_time"); 
				int saveTimeIndex = cursor.getColumnIndex("save_time"); 
	 			int imageIndex = cursor.getColumnIndex("image");//add by luo 20141008
		  
	 
				 
				int i=0;
				try{
					for(cursor.moveToFirst();!(cursor.isAfterLast()) && i<count;cursor.moveToNext()){  
		 
						int nID = cursor.getInt(idIndex);
	 					int nSaveID = cursor.getInt(saveIndex);
	 					int nAlarmID = cursor.getInt(alarmIndex);
	 					int nDevID = cursor.getInt(devIndex);
	 					int nAlarmType = cursor.getInt(alarmTypeIndex);
	 					 
						int nAlarmLevel = cursor.getInt(alarmLevelIndex);
						String strAlarmMsg = cursor.getString(alarmMsgIndex);
						String stAlarmTime = cursor.getString(alarmTimeIndex);
						long lSaveTime = cursor.getLong(saveTimeIndex);
						
						//add by luo 20141008
						byte [] imageData= cursor.getBlob(imageIndex);
						Bitmap image = null;
						if(imageData!=null && imageData.length>256){
							
							try{
								image =BitmapFactory.decodeByteArray(imageData, 0, imageData.length);
							}catch(OutOfMemoryError e){
								image = null;
							}
							
						}
						//end add by luo 20141008
						messageList[i] = new ObjectAlarmMessage(nID, nSaveID,
								nAlarmID, nDevID, nAlarmType, nAlarmLevel, 
								strAlarmMsg, stAlarmTime, lSaveTime);
						messageList[i].setImage(image);
						i++;         					
					}
				}catch(SQLException e){
					//@@System.out.println("GetAlarmMessage 6err:"+e);
				}catch(Exception e){
					//@@System.out.println("GetAlarmMessage 6err:"+e);
				}
				
			}
			
			try {      
				if(cursor!=null){
					cursor.close();
					cursor=null;
				}
				if(database!=null){
					database.close();
					database=null;
				}
	 		} catch (SQLException e) {       

			}       
		}
		
		
		
		return messageList;
	}
	
	public static ObjectAlarmMessage[] GetAlarmMessage(int nDevID, int nCount, long lStartTime, long lEndTime){
		ObjectAlarmMessage [] messageList=null;
				
		if(databaseHelper==null)  return null; 
		SQLiteDatabase database = databaseHelper.getReadableDatabase();    
		if(database==null)  return null; 
		
 		
		String sql ="select * from "+TABLE_ALARM_MESSAGE+" where dev_id="+nDevID+" and save_time>"+lStartTime+" and save_time<"+lEndTime+" order by save_time desc limit "+nCount;        
 		Cursor cursor = null;
		try {      
			cursor = database.rawQuery(sql, null);
 		} catch (SQLException e) {       
 			
 			//@@System.out.println("GetAllServerInfo SQLException: "+e.toString());
			return null;
		}       
				
 
		int count=cursor.getCount();
		if(count>0){
			messageList=new ObjectAlarmMessage[count];
		 
			int idIndex = cursor.getColumnIndex("id"); 
			int saveIndex = cursor.getColumnIndex("save_id"); 
			int alarmIndex = cursor.getColumnIndex("alarm_id"); 
			int devIndex = cursor.getColumnIndex("dev_id"); 
			int alarmTypeIndex = cursor.getColumnIndex("alarm_type");
			int alarmLevelIndex = cursor.getColumnIndex("alarm_level");
			int alarmMsgIndex = cursor.getColumnIndex("alarm_msg"); 
			int alarmTimeIndex = cursor.getColumnIndex("alarm_time"); 
			int saveTimeIndex = cursor.getColumnIndex("save_time"); 
 			int imageIndex = cursor.getColumnIndex("image");//add by luo 20141008
	  
 
			 
			int i=0;
			try{
				for(cursor.moveToFirst();!(cursor.isAfterLast()) && i<count;cursor.moveToNext()){  
	 
					int nID = cursor.getInt(idIndex);
 					int nSaveID = cursor.getInt(saveIndex);
 					int nAlarmID = cursor.getInt(alarmIndex);
 					int nDeviceID = cursor.getInt(devIndex);
 					int nAlarmType = cursor.getInt(alarmTypeIndex);
 					 
					int nAlarmLevel = cursor.getInt(alarmLevelIndex);
					String strAlarmMsg = cursor.getString(alarmMsgIndex);
					String strAlarmTime = cursor.getString(alarmTimeIndex);
					long lSaveTime = cursor.getLong(saveTimeIndex);
					
					//add by luo 20141008
					byte [] imageData= cursor.getBlob(imageIndex);
					Bitmap image = null;
					if(imageData!=null && imageData.length>256){
						
						try{
							image =BitmapFactory.decodeByteArray(imageData, 0, imageData.length);
						}catch(OutOfMemoryError e){
							image = null;
						}
						
					}
 					//end add by luo 20141008
					messageList[i] = new ObjectAlarmMessage(nID, nSaveID,
							nAlarmID, nDeviceID, nAlarmType, nAlarmLevel, 
							strAlarmMsg, strAlarmTime, lSaveTime, image);
  					i++;         					
				}
			}catch(SQLException e){
				//@@System.out.println("GetAlarmMessage 6err:"+e);
			}catch(Exception e){
				//@@System.out.println("GetAlarmMessage 6err:"+e);
			}
			
			
			 
			
		}
		
		try {      
			if(cursor!=null){
				cursor.close();
				cursor=null;
			}
			if(database!=null){
				database.close();
				database=null;
			}
 		} catch (SQLException e) {       

		}       
		
		
		return messageList;
	}
	
	public static boolean ClearAlarmMessage(){
		boolean bResult = false;
		if(databaseHelper==null)  return bResult; 
		

		SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
		if(database==null)  return bResult; 
 
		String sql ="delete from "+TABLE_ALARM_MESSAGE;   
 		try {      
			database.execSQL(sql); 
			bResult = true;
 		} catch (SQLException e) {        
			bResult = false;
		}       
 		database.close();  
		database=null;
				
		return bResult;
	}
	 //end add by luo 20141020
	

	public static boolean UpdateAlarmMessageImage(Bitmap image, int nDevID, int nAlarmID){

		boolean bResult = false;
		if(databaseHelper==null || nDevID<0 || image==null) return false;
		 
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
	        try{

	        	ByteArrayOutputStream os = new ByteArrayOutputStream();
	        	image.compress(Bitmap.CompressFormat.JPEG, 70, os); 
	    		
 	    		
	        	ContentValues values = new ContentValues();
	        	values.put("image", os.toByteArray());
	        	String[] whereArgs = {String.valueOf(nDevID), String.valueOf(nAlarmID)};
	        	String whereClause = " dev_id=? and alarm_id=?";
	        	database.update(TABLE_ALARM_MESSAGE, values, whereClause, whereArgs);
	         
	        } catch (SQLException ex) {  
	        	//@@System.out.println("UpdateAlarmMessageImage for id_"+nDevID+" fail:"+ex.toString());//add for test
	        }  
			
	        try{
	        	database.close();  
	        	database=null;
	        } catch (SQLException ex) {  
	        	//@@System.out.println("setFace for id_"+nDevID+" fail:"+ex.toString());//add for test
	        }  
		}
 		
 		return bResult;
	}
	
	//Ԥ��λ add by luo 20150803
	public static boolean IsPTZXExit(int nDevID, int nPTZXID){
		boolean bResult = false;
		if(databaseHelper==null || nDevID<=0 || nPTZXID<0)  return bResult;
		
 		SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
		if(database==null)  return bResult; 
  
		//ֻ�Ƚ�ip��ַ
 		String sql ="select count(*) from "+TABLE_PTZX_FACE+" where dev_id="+nDevID+" and ptzx_id="+nPTZXID;//modify by luo 20141227
	 
 		Cursor cursor = null;
		try {      
			cursor = database.rawQuery(sql, null);
 		} catch (SQLException e) {       
 			
 			return bResult;
		}       
				
		int count=cursor.getCount();
		if(count>0){
 
			 
			try{
				cursor.moveToFirst();
				int nCount = cursor.getInt(0);
//				//@@System.out.println("IsInfoExist: nCount="+nCount);//add for test
				if(nCount>0){
					bResult=true;
				}
			}catch(SQLException e){
				//@@System.out.println("IsInfoExist err:"+e);
			}catch(Exception e){
				//@@System.out.println("IsInfoExist err:"+e);
			}
	
		}
		
		try {      
			if(cursor!=null){
				cursor.close();
				cursor=null;
			}
			if(database!=null){
				database.close();
				database=null;
			}
 		} catch (SQLException e) {       

		}       
		
		return bResult;
	}
 
	public static boolean AddPTZXInfo(PTZXPoint ptzx){
		 
		boolean bResult = false;
		if(databaseHelper==null || ptzx==null)  return bResult;
		
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
 
			String sql ="insert into "+TABLE_PTZX_FACE+" (dev_id,ptzx_id,save_time,image) values ('"+ptzx.getnDevID()+
					"','"+ptzx.getnPTZXID()+
					"','"+ptzx.getlSaveTime()+ 
					"','"+ptzx.getFaceImage()+
					"')";   
			
	 		try {      
				database.execSQL(sql); 
				bResult = true;
	 		} catch (SQLException e) {    
//	 			System.out.println();//add for test
				bResult = false;
			}       
	 		
	 		try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
	 		
		}
 		
		return bResult;
	}
	
	
   public static boolean setPTZXFaceForDevID(int nDevID, int nPTZXID, Bitmap image){
		
 		
		boolean bResult = false;
		if(databaseHelper==null || nDevID<=0 || image==null) return false;
		 
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
	        try{

	        	ByteArrayOutputStream os = new ByteArrayOutputStream();
	        	image.compress(Bitmap.CompressFormat.JPEG, 70, os); 
	    		
//	    		//@@System.out.println("setFaceForDevID for dev_id_"+nDevID+" face size:"+os.toByteArray().length);//add for test
	    		
	        	ContentValues values = new ContentValues();
	        	values.put("face", os.toByteArray());
	        	String[] whereArgs = {String.valueOf(nDevID), String.valueOf(nPTZXID)};
	        	String whereClause = " dev_id=? and and ptzx_id=?";
	        	database.update(TABLE_PTZX_FACE, values, whereClause, whereArgs);
	        	 
	        } catch (SQLException ex) {  
	        	//@@System.out.println("setFace for id_"+nDevID+" fail:"+ex.toString());//add for test
	        }  
			
	        try{
	        	database.close();  
	        	database=null;
	        } catch (SQLException ex) {  
	        	//@@System.out.println("setFace for id_"+nDevID+" fail:"+ex.toString());//add for test
	        }  
		}
 		
 		return bResult;
 	}
   
   public static boolean updatePTZXPoint(PTZXPoint ptzx){
		
		
		boolean bResult = false;
		if(databaseHelper==null || ptzx==null || ptzx.getFaceImage()==null) return false;
		 
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
	        try{

	        	ByteArrayOutputStream os = new ByteArrayOutputStream();
	        	ptzx.getFaceImage().compress(Bitmap.CompressFormat.JPEG, 70, os); 
	    		
//	    		//@@System.out.println("setFaceForDevID for dev_id_"+nDevID+" face size:"+os.toByteArray().length);//add for test
	    		
	        	ContentValues values = new ContentValues();
	        	values.put("image", os.toByteArray());
	        	String[] whereArgs = {String.valueOf(ptzx.getnDevID()), String.valueOf(ptzx.getnPTZXID())};
	        	String whereClause = " dev_id=? and ptzx_id=?";
	        	database.update(TABLE_PTZX_FACE, values, whereClause, whereArgs);
	        	 
	        } catch (SQLException ex) {  
	        	//@@System.out.println("setFace for id_"+nDevID+" fail:"+ex.toString());//add for test
	        }  
			
	        try{
	        	database.close();  
	        	database=null;
	        } catch (SQLException ex) {  
	        	//@@System.out.println("setFace for id_"+nDevID+" fail:"+ex.toString());//add for test
	        }  
		}
		
		return bResult;
	}
  
   public static PTZXPoint[] GetPTZXInfos(int nDevID){
	   PTZXPoint [] ptzxPoints=null;
		
		if(databaseHelper==null)  return null; 
		SQLiteDatabase database = databaseHelper.getReadableDatabase();    
		if(database==null)  return null; 
		
		String sql ="select * from "+TABLE_PTZX_FACE+" where dev_id="+nDevID; 
		Cursor cursor = null;
		try {      
			cursor = database.rawQuery(sql, null);
		} catch (SQLException e) {       
			
			//@@System.out.println("GetAllServerInfo SQLException: "+e.toString());
			return null;
		}       
		
		
		int nCount=cursor.getCount();
 		
		if(nCount>0){
			ptzxPoints=new PTZXPoint[nCount];
		 
			int idIndex = cursor.getColumnIndex("id"); 
 			int ptzxIndex = cursor.getColumnIndex("ptzx_id"); 
			int saveTimeIndex = cursor.getColumnIndex("save_time"); 
			int faceIndex = cursor.getColumnIndex("image");//add by luo 20141008
 
			int i=0;
			try{
				for(cursor.moveToFirst();!(cursor.isAfterLast()) && i<nCount;cursor.moveToNext()){  
					
							
					
					int nID = cursor.getInt(idIndex);
					int nPTZXID = cursor.getInt(ptzxIndex);
					long lSaveTime = cursor.getLong(saveTimeIndex);
 
					//add by luo 20141008
					byte [] faceData= cursor.getBlob(faceIndex);
					Bitmap faceImage = null;
					if(faceData!=null && faceData.length>256){
						
						try{
							faceImage =BitmapFactory.decodeByteArray(faceData, 0, faceData.length);
						}catch(OutOfMemoryError e){
							faceImage = null;
						}
						
					}
					//end add by luo 20141008
					
					//modify by luo 20141008
					ptzxPoints[i] = new PTZXPoint(nID, nDevID, nPTZXID, lSaveTime, faceImage);
			        
					i++;         
				}
			}catch(SQLException e){
				//@@System.out.println("GetAllServerInfo 6err:"+e);
			}catch(Exception e){
				//@@System.out.println("GetAllServerInfo 6err:"+e);
			}
			
			
			 
			
		}
		
		try {      
			if(cursor!=null){
				cursor.close();
				cursor=null;
			}
			if(database!=null){
				database.close();
				database=null;
			}
		} catch (SQLException e) {       

		}       
		
		
		return ptzxPoints;
	}
	
	public static boolean ClearPTZXInfo(int nDevID){
		boolean bResult = false;
		if(databaseHelper==null)  return bResult; 
		
		synchronized (lock) {
			SQLiteDatabase database = databaseHelper.getWritableDatabase(); 
			if(database==null)  return bResult; 
	 
			String sql ="delete from "+TABLE_PTZX_FACE+" where dev_id="+nDevID;   
	 		try {      
				database.execSQL(sql); 
				bResult = true;
	 		} catch (SQLException e) {        
				bResult = false;
			}       
	 		try {      
	 			database.close();  
				database=null;
	 		} catch (SQLException e) {        
		 
			}       
		}
		
		
		////@@System.out.println("ClearServerInfo result="+bResult);//add for test
		
		return bResult;
	}
	//end add by luo 20150803
 }
